<?php

/**
 * admin actions.
 *
 * @package    sf_sandbox
 * @subpackage admin
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2692 2006-11-15 21:03:55Z fabien $
 */
class adminActions extends sfActions
{
    /**
     * Executes index action
     *
     */
    public function executeIndex()
    {
    }

    public function executeDailySalesList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');

        $dateUtil = new DateUtil();
        $q = $this->getRequestParameter('q', $dateUtil->formatDate("Y-m-d", date("Y-m-d")));
        $dateFrom = $q . " 00:00:00";
        $dateTo = $q . " 23:59:59";

        $arr = array();
        $sql = " FROM mlm_distributor dist
        LEFT JOIN (
            SELECT SUM(debit) as ECASH_SUM, dist_id FROM mlm_account_ledger WHERE account_type = 'ECASH' AND transaction_type = 'BUY ESHARE' AND created_on >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'" .
               "   GROUP BY dist_id
        ) ecash ON dist.distributor_id = ecash.dist_id
        LEFT JOIN (
            SELECT SUM(debit) as MAINTENANCE_SUM, dist_id FROM mlm_account_ledger WHERE account_type = 'MAINTENANCE' AND transaction_type = 'BUY ESHARE' AND created_on >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'" .
               "   GROUP BY dist_id
        ) maintenance ON dist.distributor_id = maintenance.dist_id
        LEFT JOIN (
            SELECT SUM(credit) as ESHARE_SUM, dist_id FROM mlm_account_ledger WHERE account_type = 'ESHARE' AND transaction_type = 'BUY' AND created_on >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'" .
               "   GROUP BY dist_id
        ) eshare ON dist.distributor_id = eshare.dist_id ";

        /******   total records  *******/
        $sWhere = " WHERE eshare.ESHARE_SUM is not null";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        if ($this->getRequestParameter('filterUsername') != "") {
            $sWhere .= " AND dist.distributor_code LIKE '%" . mysql_real_escape_string($this->getRequestParameter('filterUsername')) . "%'";
        }
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            $distCode = $resultArr['distributor_code'] == null ? "" : $resultArr['distributor_code'];

            $arr[] = array(
                $distCode,
                $distCode,
                $resultArr['ECASH_SUM'] == null ? "" : $resultArr['ECASH_SUM'],
                $resultArr['MAINTENANCE_SUM'] == null ? "" : $resultArr['MAINTENANCE_SUM'],
                $resultArr['ESHARE_SUM'] == null ? "" : $resultArr['ESHARE_SUM'],
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeDailySellShareList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');

        $dateUtil = new DateUtil();
        $q = $this->getRequestParameter('q', $dateUtil->formatDate("Y-m-d", date("Y-m-d")));
        $dateFrom = $q . " 00:00:00";
        $dateTo = $q . " 23:59:59";

        $arr = array();
        $sql = " FROM mlm_distributor dist
        LEFT JOIN (
            SELECT SUM(credit) as ECASH_SUM, dist_id FROM mlm_account_ledger WHERE account_type = 'ECASH' AND transaction_type = 'SELL ESHARE' AND created_on >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'" .
               "   GROUP BY dist_id
        ) ecash ON dist.distributor_id = ecash.dist_id
        LEFT JOIN (
            SELECT SUM(debit) as ESHARE_SUM, dist_id FROM mlm_account_ledger WHERE account_type = 'ESHARE' AND transaction_type = 'SELL' AND created_on >= '" . $dateFrom . "' AND created_on <= '" . $dateTo . "'" .
               "   GROUP BY dist_id
        ) eshare ON dist.distributor_id = eshare.dist_id ";

        /******   total records  *******/
        $sWhere = " WHERE eshare.ESHARE_SUM is not null";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        if ($this->getRequestParameter('filterUsername') != "") {
            $sWhere .= " AND dist.distributor_code LIKE '%" . mysql_real_escape_string($this->getRequestParameter('filterUsername')) . "%'";
        }
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            $distCode = $resultArr['distributor_code'] == null ? "" : $resultArr['distributor_code'];

            $arr[] = array(
                $distCode,
                $distCode,
                $resultArr['ECASH_SUM'] == null ? "" : $resultArr['ECASH_SUM'],
                $resultArr['ESHARE_SUM'] == null ? "" : $resultArr['ESHARE_SUM'],
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeWithdrawList()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();
        $sql = " FROM mlm_ecash_withdraw withdraw
        LEFT JOIN mlm_distributor dist ON withdraw.dist_id = dist.distributor_id ";

        /******   total records  *******/
        $sWhere = " WHERE withdraw.status_code = '" . $this->getRequestParameter('statusCode') . "'";

        if ($this->getRequestParameter('filterUsername') != "") {
            $sWhere .= " AND dist.distributor_code LIKE '%" . $this->getRequestParameter('filterUsername') . "%'";
        }
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        /*if ($this->getRequestParameter('filterUsername') != "") {
            $sWhere .= " AND dist.distributor_code LIKE '%" . mysql_real_escape_string($this->getRequestParameter('filterUsername')) . "%'";
        }*/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT tree_structure, " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $leaderArrs = explode(",", Globals::GROUP_LEADER);

        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            $distId = $resultArr['dist_id'] == null ? "" : $resultArr['dist_id'];
            $distCode = $resultArr['distributor_code'] == null ? "" : $resultArr['distributor_code'];

            $leader = "";
            for ($i = 0; $i < count($leaderArrs); $i++) {
                $pos = strrpos($resultArr['tree_structure'], $leaderArrs[$i]);
                if ($pos === false) { // note: three equal signs

                } else {
                    $leader = $leaderArrs[$i];
                    break;
                }
            }
            $arr[] = array(
                $resultArr['withdraw_id'] == null ? "" : $resultArr['withdraw_id'],
                $distId,
                $distCode,
                $resultArr['full_name'] == null ? "" : $resultArr['full_name'],
                $resultArr['deduct'] == null ? "" : $resultArr['deduct'],
                $resultArr['amount'] == null ? "" : $resultArr['amount'],
                $resultArr['status_code'] == null ? "" : $resultArr['status_code'],
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $resultArr['ic'] == null ? "" : $resultArr['ic'],
                $resultArr['email'] == null ? "" : $resultArr['email'],
                $resultArr['contact'] == null ? "" : $resultArr['contact'],
                $resultArr['bank_name'] == null ? "" : $resultArr['bank_name'],
                $resultArr['bank_acc_no'] == null ? "" : $resultArr['bank_acc_no'],
                $resultArr['bank_holder_name'] == null ? "" : $resultArr['bank_holder_name'],
                $resultArr['rank_code'] == null ? "" : $resultArr['rank_code'],
                $resultArr['remarks'] == null ? "" : $resultArr['remarks'],
                $leader
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeDashboard()
    {
    }

    public function executeUserList()
    {
    }

    public function executeAp()
    {
        $apService = new ApService();
        $apService->constructAp();

        echo "SUCCESS";
        return sfView::HEADER_ONLY;
    }

    public function executeDoSaveUser()
    {
        $userId = $this->getRequestParameter('userId');
        $userName = $this->getRequestParameter('userName');
        $userPassword = $this->getRequestParameter('userPassword');
        $status = $this->getRequestParameter('status');
        $role = $this->getRequestParameter('role');

        $c = new Criteria();
        $c->add(AppUserPeer::USERNAME, $userName);
        if (isset($userId) && trim($userId) != '') {
            $c->add(AppUserPeer::USER_ID, $userId, Criteria::NOT_EQUAL);
        }

        $exist = AppUserPeer::doSelectOne($c);
        if ($exist) {
            $output = array(
                "error" => true,
                "errorMsg" => "Username already exist."
            );
            echo json_encode($output);
            return sfView::HEADER_ONLY;
        }

        if (isset($userId) && trim($userId) != '') {
            $tbl_user = AppUserPeer::retrieveByPk($userId);
            if (!isset($tbl_user)) {
                $output = array(
                    "error" => true,
                    "errorMsg" => "Serious Problem happen in server."
                );
                echo json_encode($output);
                return sfView::HEADER_ONLY;
            }

            $c = new Criteria();
            $c->add(MlmAdminPeer::USER_ID, $userId);
            $tbl_admin = MlmAdminPeer::doSelectOne($c);
            if (!isset($tbl_user)) {
                $output = array(
                    "error" => true,
                    "errorMsg" => "Serious Problem happen in server."
                );
                echo json_encode($output);
                return sfView::HEADER_ONLY;
            }
        } else {
            $tbl_admin = new MlmAdmin();
            $tbl_user = new AppUser();

            $tbl_admin->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
            $tbl_admin->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
        }
        $tbl_user->setUsername($userName);
        $tbl_user->setKeepPassword($userPassword);
        $tbl_user->setUserpassword($userPassword);
        $tbl_user->setKeepPassword2($userPassword);
        $tbl_user->setUserpassword2($userPassword);
        $tbl_user->setUserRole(Globals::ROLE_ADMIN);
        $tbl_user->setStatusCode($status);
        $tbl_user->save();

        $userId = $tbl_user->getUserId();

        $tbl_admin->setAdminCode($userName);
        $tbl_admin->setUserId($userId);
        $tbl_admin->setStatusCode($status);
        $tbl_admin->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
        $tbl_admin->setAdminRole($role);
        $tbl_admin->save();

        $output = array(
            "error" => false
        );
        echo json_encode($output);
        return sfView::HEADER_ONLY;
    }

    public function executeChangePassword()
    {
        if ($this->getRequestParameter('oldPassword')) {
            $c = new Criteria();
            $c->add(AppUserPeer::USER_ID, $this->getUser()->getAttribute(Globals::SESSION_USERID));
            $exist = AppUserPeer::doSelectOne($c);

            if ($exist->getUserpassword() <> $this->getRequestParameter('oldPassword')) {
                $this->setFlash('errorMsg', "Invalid password");
            } else {
                $exist->setUserpassword($this->getRequestParameter('newPassword'));
                $exist->save();
                $this->setFlash('successMsg', "Password updated");
            }
        }
    }

    public function executeFetchRole()
    {
        $c = new Criteria();
        $c->add(AppUserRolePeer::STATUS_CODE, Globals::STATUS_ACTIVE);
        $c->addAscendingOrderByColumn(AppUserRolePeer::ROLE_CODE);
        $appUserRoles = AppUserRolePeer::doSelect($c);

        foreach ($appUserRoles as $appUserRole) {
            $arr[] = $appUserRole->getRoleCode();
        }
        echo json_encode($arr);
        return sfView::HEADER_ONLY;
    }

    public function executeUserRole()
    {
        $query = "SELECT userAccess.access_code, userAccess.menu_label, userAccess.parent_id
                    , userAccess.status_code, userAccess.tree_seq, userAccess.tree_level
                FROM app_user_access userAccess
                     WHERE userAccess.status_code = 'ACTIVE' ORDER BY userAccess.tree_seq";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $rs = $statement->executeQuery();

        $idx = 0;
        $resultArr = array();
        while ($rs->next()) {
            $arr = $rs->getRow();
            $resultArr[$idx] = $arr;
            $idx++;
        }
        $this->resultArr = $resultArr;
    }

    public function executeFetchUserAccessRole()
    {
        $roleId = $this->getRequestParameter('roleId');
        if (!isset($roleId))
            $roleId = 0;

        $userAccessArr = $this->findUserAccessRole($roleId);

        echo json_encode($userAccessArr);

        return sfView::HEADER_ONLY;
    }

    public function executeDoSaveRole()
    {
        $roleId = $this->getRequestParameter('roleId');
        $roleCode = $this->getRequestParameter('roleCode');
        $roleDesc = $this->getRequestParameter('roleDesc');
        $status = $this->getRequestParameter('status');
        $roleCodeSelected = $this->getRequestParameter('roleCodeSelected');

        $roleCodeArr = explode("|||", $roleCodeSelected);

        $c = new Criteria();
        $c->add(AppUserRolePeer::ROLE_CODE, $roleCode);
        if (isset($roleId) && trim($roleId) != '') {
            $c->add(AppUserRolePeer::ROLE_ID, $roleId, Criteria::NOT_EQUAL);
        }

        $exist = AppUserRolePeer::doSelectOne($c);
        if ($exist) {
            $output = array(
                "error" => true,
                "errorMsg" => "Role Code already exist."
            );
            echo json_encode($output);
            return sfView::HEADER_ONLY;
        }

        if (isset($roleId) && trim($roleId) != '') {
            $appUserRole = AppUserRolePeer::retrieveByPk($roleId);
            if (!isset($appUserRole)) {
                $output = array(
                    "error" => true,
                    "errorMsg" => "Serious Problem happen in server."
                );
                echo json_encode($output);
                return sfView::HEADER_ONLY;
            }

            $c = new Criteria();
            $c->add(AppUserRolePeer::ROLE_ID, $roleId);
            $appUserRole = AppUserRolePeer::doSelectOne($c);
            if (!isset($appUserRole)) {
                $output = array(
                    "error" => true,
                    "errorMsg" => "Serious Problem happen in server."
                );
                echo json_encode($output);
                return sfView::HEADER_ONLY;
            }
        } else {
            $appUserRole = new AppUserRole();
            $appUserRole->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID));
        }

        $appUserRole->setRoleCode($roleCode);
        $appUserRole->setRoleDesc($roleDesc);
        $appUserRole->setStatusCode($status);

        $appUserRole->save();

        $query = "DELETE FROM app_user_role_access WHERE role_id =" . $roleId;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $statement->executeQuery();
        foreach ($roleCodeArr as $accessCode) {
            $app_user_role_access = new AppUserRoleAccess();
            $app_user_role_access->setAccessCode($accessCode);
            $app_user_role_access->setRoleId($roleId);
            $app_user_role_access->save();
        }

        $output = array(
            "error" => false
        );
        echo json_encode($output);
        return sfView::HEADER_ONLY;
    }

    public function executeLogin()
    {
    }

    public function executeDoLogin()
    {
        $this->getUser()->clearCredentials();
        $this->getUser()->getAttributeHolder()->clear();
        $existUser = null;
        if (sfConfig::get('sf_environment') == Globals::SF_ENVIRONMENT_DEV && $this->getRequestParameter('modlgn_username') == "" && $this->getRequestParameter('modlgn_passwd') == "") {
            // ******************* uncomment for testing purpose ****************
            $existUser = AppUserPeer::retrieveByPk(1);
        } else {
            $username = trim($this->getRequestParameter('modlgn_username'));
            $password = trim($this->getRequestParameter('modlgn_passwd'));

            if ($username == '' || $password == '') {
                $this->setFlash('errorMsg', "Invalid username or password.");
                return $this->redirect('admin/login');
            }

            $this->getUser()->getAttributeHolder()->clear();

            /*	    user      	*/
            $c = new Criteria();
            $c->add(AppUserPeer::USERNAME, $username);
            $c->add(AppUserPeer::USERPASSWORD, $password);
            $c->add(AppUserPeer::USER_ROLE, Globals::ROLE_ADMIN);
            $c->add(AppUserPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
            $existUser = AppUserPeer::doSelectOne($c);
        }

        if ($existUser) {
            $c = new Criteria();
            $c->add(MlmAdminPeer::USER_ID, $existUser->getUserId());
            $existAdmin = MlmAdminPeer::doSelectOne($c);

            $this->getUser()->clearCredentials();
            $this->getUser()->setAuthenticated(true);
            $this->getUser()->addCredential($existAdmin->getAdminRole());
            $this->getUser()->addCredential("DASHBOARD");

            //var_dump($existAdmin->getAdminRole());

            $c = new Criteria();
            $c->add(AppUserRolePeer::ROLE_CODE, $existAdmin->getAdminRole());
            $exist = AppUserRolePeer::doSelectOne($c);
            if ($exist) {
                $userAccessArr = $this->findUserAccessRole($exist->getRoleId());
                foreach ($userAccessArr as $userAccess) {
                    $this->getUser()->addCredential($userAccess);
                    //var_dump($userAccess);
                }
            }
            //exit();
            $this->getUser()->setAttribute(Globals::SESSION_ADMINID, $existAdmin->getAdminId());
            $this->getUser()->setAttribute(Globals::SESSION_USERID, $existUser->getUserId());
            $this->getUser()->setAttribute(Globals::SESSION_USERNAME, $existUser->getUsername());
            $this->getUser()->setAttribute(Globals::SESSION_USERTYPE, $existAdmin->getAdminRole());

            return $this->redirect('admin/dashboard');
        }

        $this->setFlash('errorMsg', "Invalid username or password.");
        return $this->redirect('admin/login');
    }

    public function executeLoginSecurity()
    {
        $this->setFlash('errorMsg', "Login required. This page is not public.");
        return $this->redirect('admin/login');
    }

    public function executeLogout()
    {
        $this->getUser()->clearCredentials();
        $this->getUser()->getAttributeHolder()->clear();
        return $this->redirect('admin/login');
    }

    public function executeUpdateMenuIdx()
    {
        $this->getUser()->setAttribute(Globals::SESSION_ADMIN_MENU_IDX, $this->getRequestParameter('menuIdx'));
        return sfView::HEADER_ONLY;
    }

    public function executeAnnouncementList()
    {
        $this->mlm_announcements = MlmAnnouncementPeer::doSelect(new Criteria());
    }

    public function executeAnnouncementCreate()
    {
        $this->mlm_announcement = new MlmAnnouncement();

        $this->setTemplate('announcementEdit');
    }

    public function executeAnnouncementEdit()
    {
        $this->mlm_announcement = MlmAnnouncementPeer::retrieveByPk($this->getRequestParameter('announcement_id'));
        $this->forward404Unless($this->mlm_announcement);
    }

    public function executeAnnouncementUpdate()
    {
        if (!$this->getRequestParameter('announcement_id')) {
            $mlm_announcement = new MlmAnnouncement();
        }
        else
        {
            $mlm_announcement = MlmAnnouncementPeer::retrieveByPk($this->getRequestParameter('announcement_id'));
            $this->forward404Unless($mlm_announcement);
        }

        $mlm_announcement->setTitle($this->getRequestParameter('title'));
        $mlm_announcement->setTitleCn($this->getRequestParameter('title_cn'));
        $mlm_announcement->setContent($this->getRequestParameter('content'));
        $mlm_announcement->setContentCn($this->getRequestParameter('content_cn'));
        $mlm_announcement->setStatusCode($this->getRequestParameter('status_code'));
        $mlm_announcement->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_announcement->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

        $mlm_announcement->save();

        return $this->redirect('admin/announcementEdit?announcement_id=' . $mlm_announcement->getAnnouncementId());
    }

    public function executeWithdrawalListInDetail2()
    {
        $query = "SELECT withdraw.withdraw_id, withdraw.dist_id, withdraw.deduct, withdraw.created_on
                        , dist.distributor_code, dist.full_name, dist.ic, dist.email, dist.contact, dist.bank_name
                        , dist.bank_acc_no, dist.bank_holder_name, dist.rank_code
                    FROM mlm_ecash_withdraw withdraw
                        LEFT JOIN mlm_distributor dist ON dist.distributor_id = withdraw.dist_id
                    WHERE withdraw.status_code IN ('PROCESSING','PENDING')
                            ORDER BY withdraw.dist_id";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $rs = $statement->executeQuery();

        while ($rs->next()) {
            $arrs[] = $rs->getRow();
        }
        $this->arrs = $arrs;
    }

    public function executeWithdrawalListInDetail()
    {

        $response = $this->getResponse();
        $response->clearHttpHeaders();
        $response->addCacheControlHttpHeader('Cache-control', 'must-revalidate, post-check=0, pre-check=0');
        $response->setContentType('application/xls');
        $response->setHttpHeader('Content-Type', 'application/force-download', TRUE);
        $response->setHttpHeader('Content-Type', 'application/octet-stream', TRUE);
        $response->setHttpHeader('Content-Type', 'application/download', TRUE);
        $response->setHttpHeader('Content-Disposition', 'attachment; filename=withdrawal_list.xls', TRUE);
        $response->setHttpHeader('Content-Transfer-Encoding', 'binary', TRUE);
        $response->sendHttpHeaders();

        $this->xlsBOF();
        $columnIdx = 0;
        $this->xlsWriteLabel(0, $columnIdx++, "ID");
        $this->xlsWriteLabel(0, $columnIdx++, "deduct");
        $this->xlsWriteLabel(0, $columnIdx++, "created on");
        $this->xlsWriteLabel(0, $columnIdx++, "distributor code");
        $this->xlsWriteLabel(0, $columnIdx++, "full name");
        $this->xlsWriteLabel(0, $columnIdx++, "ic");
        $this->xlsWriteLabel(0, $columnIdx++, "email");
        $this->xlsWriteLabel(0, $columnIdx++, "contact");
        $this->xlsWriteLabel(0, $columnIdx++, "bank name");
        $this->xlsWriteLabel(0, $columnIdx++, "bank acc no");
        $this->xlsWriteLabel(0, $columnIdx++, "bank holder name");
        $this->xlsWriteLabel(0, $columnIdx++, "rank code");

        $query = "SELECT withdraw.withdraw_id, withdraw.dist_id, withdraw.deduct, withdraw.created_on
                        , dist.distributor_code, dist.full_name, dist.ic, dist.email, dist.contact, dist.bank_name
                        , dist.bank_acc_no, dist.bank_holder_name, dist.rank_code
                    FROM mlm_ecash_withdraw withdraw
                        LEFT JOIN mlm_distributor dist ON dist.distributor_id = withdraw.dist_id
                    WHERE withdraw.status_code IN ('PROCESSING','PENDING')
                            ORDER BY withdraw.dist_id";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $rs = $statement->executeQuery();

        $xlsRow = 1;
        while ($rs->next()) {
            $arr = $rs->getRow();
            $arrs[] = $arr;
            $columnIdx = 0;
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['withdraw_id']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['deduct']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['created_on']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['distributor_code']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['full_name']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['ic']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['email']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['contact']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['bank_name']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['bank_acc_no']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['bank_holder_name']);
            $this->xlsWriteLabel($xlsRow, $columnIdx++, $arr['rank_code']);
            $xlsRow++;
        }
        $this->xlsEOF();
        exit();
        $this->arrs = $arrs;

    }

    // #####################################
    /* ****************************
     *  excel start ~
     * *****************************/
    function xlsBOF()
    {
        echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
        return;
    }

    function xlsEOF()
    {
        echo pack("ss", 0x0A, 0x00);
        return;
    }

    function xlsWriteNumber($Row, $Col, $Value)
    {
        echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
        echo pack("d", $Value);
        return;
    }

    function xlsWriteLabel($Row, $Col, $Value)
    {
        $L = strlen($Value);
        echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
        echo $Value;
        return;
    }

    /* ****************************
     *  excel end ~
     * *****************************/
    function findUserAccessRole($roleId)
    {
        $query = "SELECT userAccess.access_code, userAccess.menu_label, userAccess.parent_id
                    , userAccess.status_code, userAccess.tree_seq, userAccess.tree_level
                    , roleAccess.role_access_id
                FROM app_user_access userAccess
                        INNER JOIN app_user_role_access roleAccess ON userAccess.access_code = roleAccess.access_code AND roleAccess.role_id = " . $roleId .
                 " WHERE userAccess.status_code = 'active' ORDER BY userAccess.tree_seq";
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $rs = $statement->executeQuery();

        while ($rs->next()) {
            $arr = $rs->getRow();
            $userAccessArr[] = $arr['access_code'];
        }

        return $userAccessArr;
    }

    function getTotalRecords($sql)
    {
        $query = "SELECT COUNT(*) AS _TOTAL " . $sql;
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $count = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_TOTAL"] != null) {
                $count = $arr["_TOTAL"];
            } else {
                $count = 0;
            }
        }
        return $count;
    }
}
